﻿

CREATE PROCEDURE [dbo].[RDIFramework_CiItems_CURD]
(
	@id int	 = null
   ,@Code nvarchar(50)
   ,@FullName nvarchar(200)
   ,@TargetTable nvarchar(50)
   ,@IsTree int = NULL
   ,@UseItemCode nvarchar(50)
   ,@UseItemName nvarchar(50)
   ,@UseItemValue nvarchar(50)
   ,@AllowEdit int = 1
   ,@AllowDelete int = 1
   ,@DeleteMark int = 0
   ,@Description nvarchar(800) = NULL
   ,@Enabled int = 1
   ,@SortCode int
   ,@CreateOn datetime = getdate
   ,@CreateUserId nvarchar(50)
   ,@CreateBy nvarchar(50)
   ,@ModifiedOn datetime = getdate
   ,@ModifyUserId nvarchar(50)
   ,@ModifiedBy nvarchar(50) 
   ,@OperationType nvarchar(30)             --操作类型,ADD：增加数据、SELECT:查询数据、UPDATE：更新数据
   ,@SuccessMark INT = 0 OUTPUT		        --成功标志 >0：操作成功 <=0,操作失败
   ,@ReturnInfo nvarchar(500) = '' OUTPUT   --操作提示信息 
)
AS
BEGIN
	SET NOCOUNT ON
	IF @OperationType IS NULL
	BEGIN
		RAISERROR ( '操作类型不能为空！', 16, 1 )
		SELECT @ReturnInfo =  '操作类型不能为空！'
		SELECT @SuccessMark = 0
		ROLLBACK TRAN
		RETURN
	END
	
	IF @OperationType IN('UPDATE')
	BEGIN
		IF @Id IS NULL
		BEGIN
			RAISERROR ( '主键为空！', 16, 1 )
			SELECT @ReturnInfo =  '主键为空！'
			SELECT @SuccessMark = 0
			ROLLBACK TRAN
		END
	END
	
	--1、查询数据
	IF @OperationType = 'SELECT' --查询数据
	BEGIN
		SELECT [Id] ,[Code],[FullName],[Enabled],[Description],[AllowEdit],[AllowDelete]
		FROM [dbo].[CiItems]
		WHERE DeleteMark = 0
	END
	
	--2、新增数据
	IF @OperationType = 'ADD' --增加数据
	BEGIN
		IF EXISTS(SELECT 1 FROM [dbo].[CiItems] 
				  WHERE (Code = @Code OR FullName = @FullName) AND DeleteMark = 0)
		BEGIN
			SELECT @ReturnInfo='已存在相同数据字典项！'
			SELECT @SuccessMark = 0
			RETURN  
		END
		
		BEGIN TRAN 
		INSERT INTO dbo.CiItems( Code ,FullName ,TargetTable ,IsTree ,UseItemCode ,UseItemName ,UseItemValue ,
		          AllowEdit ,AllowDelete ,DeleteMark ,Description ,Enabled ,SortCode ,CreateOn , CreateUserId ,
		          CreateBy ,ModifiedOn ,ModifyUserId ,ModifiedBy)
		VALUES  ( @Code, @FullName,@TargetTable,@IsTree,@UseItemCode,@UseItemName,@UseItemValue,@AllowEdit
		          ,@AllowDelete ,@DeleteMark,@Description,@Enabled,@SortCode,@CreateOn,@CreateUserId 
		          ,@CreateBy,@ModifiedOn,@ModifyUserId,@ModifiedBy)
		IF @@ROWCOUNT > 0 AND @@ERROR=0    
	    BEGIN 
			SELECT @ReturnInfo='成功新增指定数据字典！'    
			SELECT @SuccessMark = 1
			COMMIT TRAN
			RETURN
	    END    
		ELSE
		BEGIN
			SELECT @ReturnInfo='新增数据字典失败！'
			SELECT @SuccessMark = 0
			ROLLBACK TRAN
			RETURN
		END
	END
	
	--3、修改数据
	IF @OperationType = 'UPDATE' --修改数据
	BEGIN
		IF EXISTS(SELECT 1 FROM dbo.CiItems WHERE (Code = @Code OR FullName = @FullName) AND Id <> @id AND DeleteMark = 0)
		BEGIN
			SELECT @ReturnInfo='已存在相同数据字典项！'
			SELECT @SuccessMark = 0
			RETURN 
		END
		
		IF @ModifiedOn IS NULL
		BEGIN
			SELECT @ModifiedOn = GETDATE()
		END
		
		BEGIN TRAN
		UPDATE dbo.CiItems
		SET [Code] = @Code,[FullName] =@FullName,[TargetTable] = @TargetTable,[IsTree] = @IsTree,
			[UseItemCode] = @UseItemCode,[UseItemName] = @UseItemName,[UseItemValue] = @UseItemValue,
			[AllowEdit] = @AllowEdit,[AllowDelete] = @AllowDelete,[DeleteMark] = @DeleteMark,
			[Description] = @Description,[Enabled] = @Enabled,[SortCode] = @SortCode,[CreateOn] = @CreateOn,
			[CreateUserId] = @CreateUserId,[CreateBy] = @CreateBy,[ModifiedOn] = @ModifiedOn,[ModifyUserId] = @ModifyUserId,
			[ModifiedBy] = @ModifiedBy
		WHERE Id = @id
		
		IF @@ROWCOUNT > 0 AND @@ERROR=0    
	    BEGIN 
			SELECT @ReturnInfo='成功修改指定数据字典！'  
			SELECT @SuccessMark = 1   
			COMMIT TRAN
			RETURN
	    END    
		ELSE
		BEGIN
			SELECT @ReturnInfo='修改数据字典失败！'
			SELECT @SuccessMark = 0
			ROLLBACK TRAN
			RETURN
		END
	END	
	
END

